import { Injectable } from '@nestjs/common';
import { CreateGenDto } from './dto/create-gen.dto';
import { UpdateGenDto } from './dto/update-gen.dto';
import { InjectEntityManager, InjectRepository } from '@nestjs/typeorm';
import { EntityManager, In, Repository } from 'typeorm';
import { QueryGenDto } from './dto/query-gen-dto';
import { lowerKeyForArray } from 'src/utils';
import { GenTable } from './entities/gen.entity';
import { GenTableColumn } from './entities/gen-column.entity';
import { Request, Response } from 'express';
import * as archiver from 'archiver';
import { User } from 'src/system/user/entities/user.entity';
import * as ejs from 'ejs';
import { queryMap, genPathMap } from './utils/gen';
import * as path from 'path';
import { Workbook } from 'exceljs';
const fs = require('fs-extra'); // 这里只能使用require，否则会报错

@Injectable()
export class GenService {
  constructor(
    @InjectRepository(GenTable)
    private readonly genTableRepository: Repository<GenTable>,
    @InjectRepository(GenTableColumn)
    private readonly genTableColumnRepository: Repository<GenTableColumn>,
    @InjectEntityManager()
    private readonly entityManager: EntityManager,
  ) {}
  /**
   * 导入到gen_table表
   * @param createGenDto
   * @param req
   * @returns
   */
  async create(createGenDto: CreateGenDto, req: Request) {
    let user = req.user as User;
    // 将逗号分隔的字符串转换为数组
    const tableNamesArray = createGenDto.tableNames.split(',');
    // 清理数组中的空白字符（如果有的话）
    const cleanedTableNames = tableNamesArray.map((name) => name.trim());

    // 使用 query 方法执行原生 SQL 查询
    let tableResult = await this.entityManager.query(
      `SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN (${cleanedTableNames
        .map((_) => '?')
        .join(', ')})`,
      cleanedTableNames, // 传递参数数组作为第二个参数来防止 SQL 注入
    );
    tableResult = this.handleGenTable(tableResult, user.username);

    let columnResult = await this.entityManager.query(
      `SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN (${cleanedTableNames
        .map((_) => '?')
        .join(', ')})`,
      cleanedTableNames, // 传递参数数组作为第二个参数来防止 SQL 注入
    );

    try {
      return await this.entityManager.transaction(async (manager) => {
        await manager.insert(GenTable, tableResult);
        let genTableColumnResult = await manager.query(
          `SELECT table_name,table_id FROM gen_table WHERE table_name IN (${cleanedTableNames
            .map((_) => '?')
            .join(', ')})`,
          cleanedTableNames,
        );
        genTableColumnResult = this.handleGenTableColumn(
          columnResult,
          genTableColumnResult,
          user.username,
        );
        await manager.insert(GenTableColumn, genTableColumnResult);
      });
    } catch (err) {
      return err;
    }
  }

  /**
   * 代码生成的表列表
   * @param query
   */
  async findAll(query: QueryGenDto) {
    let queryBuilder = this.genTableRepository.createQueryBuilder('gen_table');
    // 处理查询条件
    if (query.table_name) {
      queryBuilder = queryBuilder.andWhere(
        'gen_table.table_name Like :tableName',
        {
          tableName: `%${query.table_name}%`,
        },
      );
    }
    if (query.table_comment) {
      queryBuilder = queryBuilder.andWhere(
        'gen_table.table_comment LIKE :tableComment',
        {
          tableComment: `%${query.table_comment}%`,
        },
      );
    }
    if (query.begin_time && query.end_time) {
      queryBuilder = queryBuilder.andWhere(
        'gen_table.create_time BETWEEN :beginTime AND :endTime',
        {
          beginTime: query.begin_time,
          endTime: query.end_time,
        },
      );
    }

    // 处理分页
    if (!query.pageNum || query.pageNum < 1) {
      query.pageNum = 1;
    }
    if (!query.pageSize || query.pageSize < 1) {
      query.pageSize = 10;
    }
    queryBuilder = queryBuilder
      .skip((query.pageNum - 1) * query.pageSize)
      .take(query.pageSize)
      .orderBy('gen_table.table_name', 'ASC');
    let total = await queryBuilder.getCount();
    let list = await queryBuilder.getMany();
    return {
      pageNum: query.pageNum,
      pageSize: query.pageSize,
      total,
      list,
    };
  }

  /**
   * 修改代码生成的表
   * @param id
   * @param updateGenDto
   * @returns
   */
  async update(id: number, updateGenDto: UpdateGenDto, req: Request) {
    let exitUser = req.user as User;
    let baseForm = updateGenDto.baseForm;
    let genForm = updateGenDto.genForm;
    let columns = updateGenDto.columns;
    let genTable = await this.genTableRepository.findOne({
      where: {
        table_id: id,
      },
    });
    genTable.update_by = exitUser.username;
    genTable.table_name = baseForm.table_name;
    genTable.table_comment = baseForm.table_comment;
    genTable.function_author = baseForm.function_author;
    if (baseForm.class_name) {
      genTable.class_name = baseForm.class_name;
    }
    if (baseForm.remark) {
      genTable.remark = baseForm.remark;
    }
    genTable.tpl_category = genForm.tpl_category;
    genTable.module_name = genForm.module_name;
    genTable.business_name = genForm.business_name;
    genTable.function_name = genForm.function_name;
    genTable.gen_type = genForm.gen_type;
    genTable.gen_path = genForm.gen_path;
    genTable.is_extends_baseEntity = genForm.is_extends_baseEntity;
    genTable.parent_menu_id = genForm.parent_menu_id;

    columns = columns.map((column) => {
      if (column.column_id) {
        column.update_by = exitUser.username;
        return column;
      } else {
        column.create_by = exitUser.username;
        return column;
      }
    });
    try {
      return await this.entityManager.transaction(async (manager) => {
        await manager.update(GenTable, { table_id: id }, genTable);
        for (let i = 0; i < columns.length; i++) {
          await manager.update(
            GenTableColumn,
            { column_id: columns[i].column_id },
            columns[i],
          );
        }
      });
    } catch (err) {
      return err;
    }
  }

  /**
   * 删除代码生成的表
   */
  async remove(ids: number[]) {
    try {
      await this.entityManager.transaction(async (manager) => {
        await manager.delete(GenTable, {
          table_id: In(ids),
        });
        await manager.delete(GenTableColumn, {
          table_id: In(ids),
        });
      });
    } catch (err) {
      return err;
    }
  }

  /**
   * 根据查询条件查询数据库中所有的表
   * @param query
   * @returns
   */
  async getDBTableNames(query: QueryGenDto) {
    // 构建 WHERE 子句
    let whereConditions = [];
    let params: any[] = [];

    // 如果有 gen_table 表，确保排除其中的表名和'gen_table', 'gen_table_column'
    const genTableNames = await this.entityManager.query(
      `SELECT table_name FROM gen_table`,
    );
    let excludedTableNames = genTableNames.map((row) => row.table_name);
    excludedTableNames.push('gen_table', 'gen_table_column');

    // 添加排除表名参数
    params.push(...excludedTableNames);

    if (query.table_name) {
      whereConditions.push(`TABLE_NAME LIKE CONCAT('%', ?, '%')`);
      params.push(query.table_name);
    }

    if (query.table_comment) {
      whereConditions.push(`TABLE_COMMENT LIKE CONCAT('%', ?, '%')`);
      params.push(query.table_comment);
    }

    if (query.begin_time && query.end_time) {
      whereConditions.push(`CREATE_TIME BETWEEN ? AND ?`);
      params.push(query.begin_time, query.end_time);
    }

    // 构建完整的 SQL 查询语句
    let sql = `  
      SELECT *
      FROM INFORMATION_SCHEMA.TABLES  
      WHERE TABLE_SCHEMA = DATABASE()`;
    sql += ` AND TABLE_NAME NOT IN (${excludedTableNames
      .map(() => '?')
      .join(',')})`;
    if (whereConditions.length > 0) {
      sql += 'AND ' + whereConditions.join(' AND ');
    }
    sql += `ORDER BY TABLE_NAME  
      LIMIT ? OFFSET ?  
    `;

    // 添加分页参数
    params.push(Number(query.pageSize));
    params.push(Number(query.pageSize) * (Number(query.pageNum) - 1));

    // 执行原生 SQL 查询
    const result = await this.entityManager.query(sql, params);

    // 返回查询结果
    return {
      pageNum: query.pageNum,
      pageSize: query.pageSize,
      total: result ? result.length : 0,
      list: lowerKeyForArray(result),
    };
  }

  /**
   * 将查询到的表信息处理为genTable对象
   * @param data
   * @param username
   */
  handleGenTable(data, username) {
    return data.map((k) => {
      return {
        table_name: k.TABLE_NAME,
        table_comment: k.TABLE_COMMENT,
        sub_table_name: null,
        sub_table_fk_name: null,
        class_name: null,
        tpl_category: 'crud',
        package_name: null,
        module_name: 'system',
        business_name: k.TABLE_NAME,
        function_name: k.TABLE_COMMENT,
        function_author: 'Linxi',
        gen_type: '0',
        gen_path: null,
        options: null,
        create_by: username,
      };
    });
  }

  /**
   * 根据查询到的表信息处理为genTableColumn对象
   * @param data  数据表字段
   * @param table 数据表名、id的集合
   * @param username
   * @returns
   */
  handleGenTableColumn(data, table, username) {
    return data.map((k) => {
      let table_id = table.find((v) => v.table_name == k.TABLE_NAME).table_id;
      const regex = /\b([a-zA-Z]+)(\(\d+\))/;
      // 执行匹配
      let matches = k.COLUMN_TYPE.match(regex);
      return {
        table_id,
        column_name: k.COLUMN_NAME,
        column_comment: k.COLUMN_COMMENT,
        column_type: matches ? matches[1] : k.COLUMN_TYPE,
        column_length: matches ? matches[2].slice(1, -1) : null,
        js_type: this.mapTypeToTypeScript(k.COLUMN_TYPE),
        is_pk: k.COLUMN_KEY == 'PRI' ? '1' : '0',
        is_increment: k.EXTRA == 'auto_increment' ? '1' : '0',
        is_nullable: k.IS_NULLABLE == 'YES' ? '1' : '0',
        is_required: 0,
        is_insert: 1,
        is_edit: 0,
        is_list: 0,
        is_query: 0,
        query_type: 'EQ',
        html_type: 'input',
        dict_type: null,
        sort: k.ORDINAL_POSITION,
        create_by: username,
      };
    });
  }

  /**
   * 根据table_id获取数据表的信息、字段列表
   * @param query
   * @returns
   */
  async getTableColumn(query: { id: number; pageNum: number }) {
    // 表字段列表
    let columns = await this.genTableColumnRepository.find({
      where: {
        table_id: query.id,
      },
    });
    // 表信息
    let info = await this.genTableRepository.findOne({
      where: {
        table_id: query.id,
      },
    });
    return {
      info,
      columns,
    };
  }

  /**
   * 预览代码
   * @param id
   */
  async preview(id) {
    const table = await this.getTableColumn({
      id,
      pageNum: 1,
    });
    let vue = await ejs.renderFile('src/tool/gen/views/front/vue.ejs', {
      query: table.columns.filter((k) => k.is_query == '1'),
      list: table.columns.filter((k) => k.is_list == '1'),
      edit: table.columns.filter((k) => k.is_edit == '1'),
      insert: table.columns.filter((k) => k.is_insert == '1'),
      required: table.columns.filter((k) => k.is_required == '1'),
      queryBetweens: table.columns.filter(
        (k) => k.is_query == '1' && k.query_type == 'BETWEEN',
      ),
      tableInfo: table.info,
      signPre: table.info.module_name + ':' + table.info.business_name, // 权限字符前缀
      primaryKey:
        table.columns.find((k) => k.is_pk == '1')?.column_name || 'id', // 主键
      className: this.capitalizeWords(table.info.table_name),
    });
    let api = await ejs.renderFile('src/tool/gen/views/front/api.ejs', {
      options: table.info,
      className: this.capitalizeWords(table.info.table_name),
    });
    let entity = await ejs.renderFile('src/tool/gen/views/back/entity.ejs', {
      table,
      className: this.capitalizeWords(table.info.table_name),
    });

    let dto = await ejs.renderFile('src/tool/gen/views/back/dto.ejs', {
      query: table.columns.filter((k) => k.is_query == '1'),
      tableInfo: table.info,
      className: this.capitalizeWords(table.info.table_name),
    });

    let controller = await ejs.renderFile(
      'src/tool/gen/views/back/controller.ejs',
      {
        tableInfo: table.info,
        className: this.capitalizeWords(table.info.table_name),
        permissionPrefix:
          table.info.module_name + ':' + table.info.business_name,
      },
    );

    let module = await ejs.renderFile('src/tool/gen/views/back/module.ejs', {
      tableInfo: table.info,
      className: this.capitalizeWords(table.info.table_name),
    });

    let service = await ejs.renderFile('src/tool/gen/views/back/service.ejs', {
      tableInfo: table.info,
      column: table.columns,
      query: table.columns
        .filter((k) => k.is_query == '1')
        .map((k) => {
          return {
            ...k,
            query_type_map: queryMap[k.query_type],
          };
        }),
      className: this.capitalizeWords(table.info.table_name),
      primaryKey:
        table.columns.find((k) => k.is_pk == '1')?.column_name || 'id', // 主键
    });

    let sql = await ejs.renderFile('src/tool/gen/views/back/sql.ejs', {
      functionName: table.info.function_name,
      parentMenuId: table.info.parent_menu_id,
      businessName: table.info.business_name,
      moduleName: table.info.module_name,
      permissionPrefix: table.info.module_name + ':' + table.info.business_name,
    });
    return {
      table_info: table.info,
      codes: {
        vue,
        api,
        entity,
        dto,
        controller,
        module,
        service,
        sql,
      },
    };
  }
  /**
   * 将首字母转为大写
   * @param str
   * @returns
   */
  capitalizeWords(str) {
    // 使用正则表达式分割字符串，空格或下划线都会被匹配
    let words = str.split(/\s|_/);
    for (let i = 0; i < words.length; i++) {
      words[i] = words[i].charAt(0).toUpperCase() + words[i].slice(1);
    }
    return words.join('');
  }
  /**
   * 将MySQL的数据类型转为TS类型（简单版）
   * @param sqlType
   * @returns
   */
  mapTypeToTypeScript(sqlType) {
    // 假设我们得到了类似 'varchar(255)' 这样的字符串
    // 我们需要去掉括号中的长度限制
    const typeWithoutLength = sqlType.replace(/\(\d+\)/, '');

    switch (typeWithoutLength.toLowerCase()) {
      case 'varchar':
      case 'text':
      case 'char':
        return 'string';
      case 'int':
      case 'tinyint':
      case 'smallint':
      case 'mediumint':
      case 'bigint':
        return 'number';
      case 'float':
      case 'double':
      case 'decimal':
        return 'number'; // 或者考虑使用 'BigDecimal' 或其他类型库
      case 'datetime':
      case 'timestamp':
        return 'Date'; // 或者使用 'moment.Moment' 或其他日期时间库
      case 'date':
        return 'Date';
      case 'boolean':
      case 'tinyint(1)': // 假设tinyint(1)用于布尔值
        return 'boolean';
      case 'enum':
        // 对于枚举类型，你可能需要单独处理，因为它需要指定可能的值
        // 这里可以返回一个字符串或其他表示，但具体实现会复杂一些
        return 'string'; // 临时使用字符串表示
      case 'json':
        // 如果你的数据库支持 JSON 类型
        return 'any'; // 或者使用 JSON 对象或特定类型
      default:
        return 'any'; // 对于不支持的类型，返回 any 或其他默认类型
    }
  }

  /**
   * 生成代码（压缩包）
   */
  async genCode(id, res: Response) {
    let content = await this.preview(id);
    // 设置响应头，告知客户端这是一个 ZIP 文件
    res.setHeader('Content-Type', 'application/zip');
    let filename = content.table_info.table_name;
    res.setHeader(
      'Content-Disposition',
      'attachment; filename=' + filename + '.zip',
    );

    // 创建一个 ZIP 归档
    const archive = archiver('zip', {
      zlib: { level: 9 }, // 设置压缩级别
    });

    // 监听错误
    archive.on('error', function (err) {
      throw err;
    });

    // 监听 'warn' 事件以处理非致命警告
    archive.on('warning', function (err) {
      if (err.code === 'ENOENT') {
        console.warn(err);
      } else {
        throw err;
      }
    });
    // 文件路径映射
    let fileMap = genPathMap(
      content.table_info.module_name,
      content.table_info.table_name,
    );
    // 遍历所有需要生成的文件，并将其添加到归档中
    Object.entries(content.codes).forEach(([key, value]) => {
      archive.append(value, { name: fileMap[key] });
    });
    // 结束归档
    archive.finalize();

    // 将 ZIP 归档的流直接发送给响应
    archive.pipe(res);
  }

  /**
   * 生成代码（自定义路径）
   * @param id
   * @returns
   */
  async genTable(id) {
    let content = await this.preview(id);
    // 磁盘绝对路径,若不填写则生成到当前Web项目下
    let gen_path = content.table_info.gen_path ?? path.join(process.cwd(), '');
    // 文件路径映射
    let fileMap = genPathMap(
      content.table_info.module_name,
      content.table_info.table_name,
    );
    for (const [key, value] of Object.entries(content.codes)) {
      try {
        // 组装完整路径
        let fullPath = path.join(gen_path, fileMap[key]);
        // 确保路径存在，否则自动创建目录
        fs.ensureDir(path.dirname(fullPath)).then(() => {
          // 写入文件
          fs.writeFile(fullPath, value, 'utf8');
        });
      } catch (err) {
        return err;
      }
    }
    return gen_path;
  }

  /**
   * 导出数据表
   * @param res
   */
  async exportDataTable(res: Response) {
    const workbook = new Workbook();

    // 获取所有表信息
    const tables = await this.entityManager.query(`
        SELECT TABLE_NAME AS tableName, TABLE_COMMENT AS tableComment
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
      `);

    for (const table of tables) {
      // 创建带有效名称的工作表
      const worksheet = workbook.addWorksheet(
        this.sanitizeSheetName(table.tableName),
      );

      // 设置中文表头
      worksheet.columns = [
        { header: '字段名称', key: 'fieldName', width: 15 },
        { header: '字段类型', key: 'fieldType', width: 15 },
        { header: '字段长度', key: 'fieldLength', width: 10 },
        { header: '是否主键', key: 'isPrimary', width: 15 },
        { header: '是否允许为空', key: 'nullable', width: 15 },
        { header: '备注', key: 'comment', width: 20 },
      ];

      // 设置表头样式
      worksheet.getRow(1).eachCell((cell) => {
        cell.font = { bold: true };
        cell.alignment = { vertical: 'middle', horizontal: 'center' };
      });

      // 获取字段信息
      const columns = await this.entityManager.query(`
          SELECT 
            COLUMN_NAME AS fieldName,
            COLUMN_TYPE AS rawType,
            CHARACTER_MAXIMUM_LENGTH AS maxLength,
            IS_NULLABLE AS nullable,
            COLUMN_COMMENT AS comment
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = '${table.tableName}'
            AND TABLE_SCHEMA = DATABASE()
        `);

      // 获取主键信息
      const primaryKeys = await this.entityManager.query(`
          SELECT COLUMN_NAME 
          FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
          WHERE TABLE_NAME = '${table.tableName}'
            AND CONSTRAINT_NAME = 'PRIMARY'
            AND TABLE_SCHEMA = DATABASE()
        `);

      const pkSet = new Set(primaryKeys.map((pk) => pk.COLUMN_NAME));

      // 添加数据行
      const rows = columns.map((column) => ({
        fieldName: column.fieldName,
        fieldType: this.parseType(column.rawType).type,
        fieldLength: this.parseType(column.rawType).length,
        isPrimary: pkSet.has(column.fieldName) ? '是' : '否',
        nullable: column.nullable === 'YES' ? '是' : '否',
        comment: column.comment || '',
      }));

      worksheet.addRows(rows);

      // 设置数据行样式
      worksheet.eachRow(null, (row, rowNumber) => {
        row.eachCell((cell) => {
          cell.border = {
            top: { style: 'thin' },
            left: { style: 'thin' },
            bottom: { style: 'thin' },
            right: { style: 'thin' },
          };
        });
      });

      // 自动调整列宽
      worksheet.columns.forEach((column) => {
        let maxLength = 0;
        column.eachCell({ includeEmpty: true }, (cell) => {
          const length = cell.value ? cell.value.toString().length : 0;
          if (length > maxLength) maxLength = length;
        });
        column.width = Math.min(Math.max(maxLength + 2, column.width || 0), 50);
      });
    }


    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    );
    // 保存文件
    const filename = encodeURIComponent('数据表.xlsx');
    res.setHeader(
      'Content-Disposition',
      'attachment; filename=' + filename + '',
    );
    res.setHeader('Cache-Control', 'max-age=0');
    const file = await workbook.xlsx.writeBuffer();
    res.end(file);
  }

  private parseType(rawType: string): { type: string; length: string } {
    const typeMatch = rawType.match(/^([a-zA-Z]+)/);
    const type = typeMatch ? typeMatch[1].toUpperCase() : 'UNKNOWN';

    const lengthMatch = rawType.match(/\(([\d,]+)\)/);
    return {
      type,
      length: lengthMatch ? lengthMatch[1] : '',
    };
  }

  private sanitizeSheetName(name: string): string {
    // 替换非法字符
    let safeName = name.replace(/[\\/*\[\]:?]/g, '_');
    // 截断超过31字符
    return safeName.substring(0, 31);
  }
}
